//
//  StorePersistentManager.m
//  iFoundIt
//
//  Created by Navid Shariat on 20/05/10.
//  Copyright 2010 QUT. All rights reserved.
//

#import "StorePersistentManager.h"
#import <sqlite3.h>
#import "Store.h"

//const NSString * sqlFileName=@"iFoundit.sqlite";
const NSString * Col_Id=@"StoreID";
const NSString * Col_Name=@"StoreName";
const NSString * Col_PostCode=@"postCode";
const NSString * Col_Street=@"street";
const NSString * Col_Suburb=@"suburb";
const NSString * Col_SearchKeywords=@"searchKeywords";
const NSString * Table_Name=@"Store";

static sqlite3 *database = nil;
static NSString * dbname=@"ifoundit.sqlite";

@implementation StorePersistentManager

- (void) dealloc{
//	[sqlFileName release];
	[Col_Id release];
	[Col_Name release];
	[Col_PostCode release];
	[Col_Street release];
	[Col_Suburb release];
	[Col_SearchKeywords release];
	[Table_Name release];
	[super dealloc];
}

- (NSString *) getDBPath {
	
	//Search for standard documents using NSSearchPathForDirectoriesInDomains
	//First Param = Searching the documents directory
	//Second Param = Searching the Users directory and not the System
	//Expand any tildes and identify home directories.
	NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory , NSUserDomainMask, YES);
	NSString *documentsDir = [paths objectAtIndex:0];
	//	return [documentsDir stringByAppendingPathComponent:@"iFoundit.sqlite"];
//	[paths release];
	return [documentsDir stringByAppendingPathComponent:dbname];	
}


- (char *) getSelectAllString{
	
/*	char* selectStr= [NSString stringWithFormat:@"select %@,%@,%@,%@,%@,%@ from %@", 
			Col_Id,Col_Name,Col_PostCode,Col_Street,Col_Suburb] to; */
	char * selectStr= @"select StoreID, StoreName, postCode, street, suburb from Store"; 
	return selectStr;
	}

- (NSMutableArray * ) getStores{
	NSMutableArray * stores= [[NSMutableArray alloc] init];
	NSString * dbPath=[self getDBPath];
	NSLog(dbPath);
	if (sqlite3_open([dbPath UTF8String], &database) == SQLITE_OK) {
		
		const char * sql = "SELECT * FROM Shop"; 
		sqlite3_stmt *selectstmt;
		int sqlResult=sqlite3_prepare_v2(database, sql, -1, &selectstmt, NULL);
		NSLog([NSString stringWithFormat:@"%i", sqlResult] );
		if(sqlResult == SQLITE_OK) {
			
			while(sqlite3_step(selectstmt) == SQLITE_ROW) {
				
				Store * store=[[Store alloc]init];
				[store setStoreId:sqlite3_column_int(selectstmt, 0)];
				[store setShopName: [NSString  stringWithUTF8String:sqlite3_column_text(selectstmt,1)]];
				[store setSeachKeywords: [NSString  stringWithUTF8String:sqlite3_column_text(selectstmt,2)]];
				[store setLatitude:sqlite3_column_double(selectstmt, 3)];
				[store setLongtitude:sqlite3_column_double(selectstmt, 4)];
				[stores addObject: store]; 
				[store release];
			}
		}
	}
	else
		sqlite3_close(database); //Even though the open call failed, close the database connection to release all the memory.
//	[dbPath release];
	return stores;
}

- (NSMutableArray * ) findStores: (NSString *) searchKeyword{
	NSMutableArray * stores= [[NSMutableArray alloc] init];
	NSString * dbPath=[self getDBPath];
	if (sqlite3_open([dbPath UTF8String], &database) == SQLITE_OK) {
		NSLog(searchKeyword);
		NSString * query = [NSString stringWithFormat:@"SELECT * FROM Shop where SearchKeyword LIKE '%c%@%c'",'%',searchKeyword,'%']; 
		NSLog(query);
		
		const char * sql = [query UTF8String];
//		[query release];
		sqlite3_stmt *selectstmt;
		int sqlResult=sqlite3_prepare_v2(database, sql, -1, &selectstmt, NULL);
		if(sqlResult == SQLITE_OK) {
			
			while(sqlite3_step(selectstmt) == SQLITE_ROW) {				
				Store * store=[[Store alloc]init];
				[store setStoreId:sqlite3_column_int(selectstmt, 0)];
				[store setShopName: [NSString  stringWithUTF8String:sqlite3_column_text(selectstmt,1)]];
				[store setSeachKeywords: [NSString  stringWithUTF8String:sqlite3_column_text(selectstmt,2)]];
				[store setLatitude:sqlite3_column_double(selectstmt, 3)];
				[store setLongtitude:sqlite3_column_double(selectstmt, 4)];
				[stores addObject: store]; 
				[store release];
			}
		}
	}
	else
		sqlite3_close(database); //Even though the open call failed, close the database connection to release all the memory.
//	[dbPath release];
	return stores;
	
}

- (NSMutableArray * ) findStores: (NSString *) searchKeyword : (CLLocation*) fromLocation:(double) withinDistance{
	NSMutableArray * stores= [[NSMutableArray alloc] init];
	NSString * dbPath=[self getDBPath];
	if (sqlite3_open([dbPath UTF8String], &database) == SQLITE_OK) {
		NSLog(searchKeyword);
		NSString * query = [NSString stringWithFormat:@"SELECT * FROM Shop where SearchKeyword LIKE '%c%@%c'",'%',searchKeyword,'%']; 
		NSLog(query);
		
		const char * sql = [query UTF8String];
		//		[query release];
		sqlite3_stmt *selectstmt;
		int sqlResult=sqlite3_prepare_v2(database, sql, -1, &selectstmt, NULL);
		if(sqlResult == SQLITE_OK) {
			
			while(sqlite3_step(selectstmt) == SQLITE_ROW) {				
				Store * store=[[Store alloc]init];
				[store setStoreId:sqlite3_column_int(selectstmt, 0)];
				[store setShopName: [NSString  stringWithUTF8String:sqlite3_column_text(selectstmt,1)]];
				[store setSeachKeywords: [NSString  stringWithUTF8String:sqlite3_column_text(selectstmt,2)]];
				[store setLatitude:sqlite3_column_double(selectstmt, 3)];
				[store setLongtitude:sqlite3_column_double(selectstmt, 4)];
				double distance= [[store getLocation] getDistanceFrom:fromLocation];
				if (distance<=withinDistance){	
					[store setDistance:distance];
					[stores addObject: store]; 
				}
				[store release];
			}
		}
	}
	else
		sqlite3_close(database); //Even though the open call failed, close the database connection to release all the memory.
	//	[dbPath release];
	return stores;
	
}

- (BOOL) storeExists: (NSString *) searchKeyword : (CLLocation*) fromLocation:(double) withinDistance{
	NSString * dbPath=[self getDBPath];
	if (sqlite3_open([dbPath UTF8String], &database) == SQLITE_OK) {
		NSLog(searchKeyword);
		NSString * query = [NSString stringWithFormat:@"SELECT * FROM Shop where SearchKeyword LIKE '%c%@%c'",'%',searchKeyword,'%']; 
		NSLog(query);
		
		const char * sql = [query UTF8String];
		//		[query release];
		sqlite3_stmt *selectstmt;
		int sqlResult=sqlite3_prepare_v2(database, sql, -1, &selectstmt, NULL);
		if(sqlResult == SQLITE_OK) {
			
			while(sqlite3_step(selectstmt) == SQLITE_ROW) {				
				Store * store=[[Store alloc]init];
				[store setStoreId:sqlite3_column_int(selectstmt, 0)];
				[store setShopName: [NSString  stringWithUTF8String:sqlite3_column_text(selectstmt,1)]];
				[store setSeachKeywords: [NSString  stringWithUTF8String:sqlite3_column_text(selectstmt,2)]];
				[store setLatitude:sqlite3_column_double(selectstmt, 3)];
				[store setLongtitude:sqlite3_column_double(selectstmt, 4)];
				double distance= [[store getLocation] getDistanceFrom:fromLocation];
				if (distance<=withinDistance){						
					[store release];
					sqlite3_reset(selectstmt);					
					sqlite3_close(database);
					return YES;
				}
				[store release];
			}
		}
	}
	else
		sqlite3_close(database); //Even though the open call failed, close the database connection to release all the memory.
	return NO;
	
}

- (NSMutableArray * ) getCategories{
	NSMutableArray * categories= [[NSMutableArray alloc] init];
	NSString * dbPath=[self getDBPath];
	NSLog(dbPath);
	if (sqlite3_open([dbPath UTF8String], &database) == SQLITE_OK) {
		
		const char * sql = "SELECT * FROM ShopCategory ORDER BY CategoryName"; 
		sqlite3_stmt *selectstmt;
		int sqlResult=sqlite3_prepare_v2(database, sql, -1, &selectstmt, NULL);
		NSLog([NSString stringWithFormat:@"%i", sqlResult] );
		if(sqlResult == SQLITE_OK) {
			
			while(sqlite3_step(selectstmt) == SQLITE_ROW) {
				[categories addObject: [NSString  stringWithUTF8String:sqlite3_column_text(selectstmt,1)]];
			}
		}
	}
	else
		sqlite3_close(database); //Even though the open call failed, close the database connection to release all the memory.
	//	[dbPath release];
	return categories;
}


@end
